package com.crm.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.annotation.Transactional;

import com.crm.bean.Customer;
import com.crm.service.CrmService;

@Transactional
public class CrmServiceImpl implements CrmService {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Customer> findAll() {
		String sql = "select * from t_customer";
		List<Customer> customers = jdbcTemplate.query(sql, new RowMapper<Customer>() {
			public Customer mapRow(ResultSet result, int arg1) throws SQLException {
				int id = result.getInt("id");
				String name = result.getString("name");
				String station = result.getString("station");
				String telephone = result.getString("telephone");
				String address = result.getString("address");
				String decidedzone_id = result.getString("decidedzone_id");
				// 返回一个customer对象 就会自动帮你add 返回一个list集合
				return new Customer(id, name, station, telephone, address, decidedzone_id);
			}
		});
		return customers;
	}

	// 查询已经关联的客户
	public List<Customer> findCusByDecidedId(String decidedId) {
		String sql = "select * from t_customer where decidedzone_id = ?";
		List<Customer> customers = jdbcTemplate.query(sql, new RowMapper<Customer>() {
			public Customer mapRow(ResultSet result, int arg1) throws SQLException {
				int id = result.getInt("id");
				String name = result.getString("name");
				String station = result.getString("station");
				String telephone = result.getString("telephone");
				String address = result.getString("address");
				String decidedzone_id = result.getString("decidedzone_id");
				// 返回一个customer对象 就会自动帮你add 返回一个list集合
				return new Customer(id, name, station, telephone, address, decidedzone_id);
			}
		}, decidedId);
		return customers;
	}

	// 查询未关联的客户
	public List<Customer> findCusNoDecided() {
		String sql = "select * from t_customer where decidedzone_id is null";
		List<Customer> customers = jdbcTemplate.query(sql, new RowMapper<Customer>() {
			public Customer mapRow(ResultSet result, int arg1) throws SQLException {
				int id = result.getInt("id");
				String name = result.getString("name");
				String station = result.getString("station");
				String telephone = result.getString("telephone");
				String address = result.getString("address");
				String decidedzone_id = result.getString("decidedzone_id");
				// 返回一个customer对象 就会自动帮你add 返回一个list集合
				return new Customer(id, name, station, telephone, address, decidedzone_id);
			}
		});
		return customers;
	}

	public void assigncustomerstodecidedzone(String decidedId, List<String> ids) {
		String sql1 = "update t_customer set decidedzone_id = null where decidedzone_id = ?";
		jdbcTemplate.update(sql1, decidedId);
		// 给定区关联 客户
		String sql2 = "update t_customer set decidedzone_id = ? where id = ?";
		if (ids != null && ids.size() > 0) {
			// 循环更新
			for (String id : ids) {
				jdbcTemplate.update(sql2, decidedId, id);
			}
		}
	}
	//通过电话号码查询
	public Customer findCusByTel(String telephone) {
		String sql = "select * from t_customer where telephone = ?";
		List<Customer> customers = jdbcTemplate.query(sql, new RowMapper<Customer>() {
			public Customer mapRow(ResultSet result, int arg1) throws SQLException {
				int id = result.getInt("id");
				String name = result.getString("name");
				String station = result.getString("station");
				String telephone = result.getString("telephone");
				String address = result.getString("address");
				String decidedzone_id = result.getString("decidedzone_id");
				// 返回一个customer对象 就会自动帮你add 返回一个list集合
				return new Customer(id, name, station, telephone, address, decidedzone_id);
			}
		},telephone);
		if(customers != null && customers.size()>0){
			return customers.get(0);
		}
		return null;
	}
	
	//通过 地址查询定区的Id
	public String findDecidedIdByAddr(String address) {
		String sql = "select decidedzone_id from t_customer where address = ?";
		String decidedId = jdbcTemplate.queryForObject(sql, String.class, address);
		return decidedId;
	}
}
